package jsj.myjdbc;


import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class FrameShowData extends JFrame implements ActionListener{
	DefaultTableModel dt;
	JTable table;
	JScrollPane sp;
	String[][]data = new String[60][4];
	String[] columnNames = {"学号","姓名","年龄","性别"};
	JButton btn_delete,btn_add,btn_search;
	JPanel p;
	JTextField tf_sno;
	JLabel lbl_sno;
	
	public FrameShowData() {
		// TODO Auto-generated constructor stub
		dt = new DefaultTableModel(data, columnNames);
		showData();
		table = new JTable(dt);
		sp = new JScrollPane(table);
		btn_delete = new JButton("查询");
		btn_add = new JButton("添加");
		btn_search = new JButton("查询");
		btn_delete.addActionListener(this);
		btn_add.addActionListener(this);
		btn_search.addActionListener(this);
		p = new JPanel();
		tf_sno = new JTextField(20);
		lbl_sno = new JLabel("学号");
		
		p.add(lbl_sno);
		p.add(tf_sno);
//		p.add(btn_add);
//		p.add(btn_delete);
		p.add(btn_search);
		
		this.add(p,BorderLayout.NORTH);
		this.add(sp);
		this.setBounds(200,200,400,500);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		
	}

	public static void main(String[] args) {
		FrameShowData f = new FrameShowData();
		f.setVisible(true);

	}

	@Override
	public void actionPerformed(ActionEvent e) {
		Connection conn = DBUtil.getConnection();
		try {
			Statement stmt = conn.createStatement();
			
			if(e.getSource() == btn_delete) {
				//stmt.execute("delete from student where sno=95005");
				PreparedStatement pstmt = conn.prepareStatement("delete from student where sno=?");
				pstmt.setString(1, tf_sno.getText());
				pstmt.executeUpdate();
				showData();
			}
			
			if(e.getSource() == btn_add) {
//				stmt.execute("insert into student values('95005','zhouyu',19,'male')");
				PreparedStatement pstmt = conn.prepareStatement("insert into student values(?,?,?,?)");
				pstmt.setString(1, "95003");
				pstmt.setString(2, "diaochan");
				pstmt.setInt(3, 19);
				pstmt.setString(4, "female");
				pstmt.executeUpdate();
				showData();
			}
			
			if(e.getSource() == btn_search) {
				//stmt.execute("delete from student where sno=95005");
				PreparedStatement pstmt = conn.prepareStatement("select * from student where sno=?");
				pstmt.setString(1, tf_sno.getText());
				ResultSet rs = pstmt.executeQuery();
				int i = 0;
				while(rs.next()) {
					data[i][0] = rs.getString(1);
					data[i][1] = rs.getString(2);
					data[i][2] = String.valueOf(rs.getInt(3));
					data[i][3] = rs.getString(4);
					i++;
					
				}
				dt.setDataVector(data, columnNames);
			
			}
			
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
	}
	
	public void showData() {
		String[][]data = new String[60][4];
		Connection conn = DBUtil.getConnection();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from student");
			int i = 0;
			while(rs.next()) {
				data[i][0] = rs.getString(1);
				data[i][1] = rs.getString(2);
				data[i][2] = String.valueOf(rs.getInt(3));
				data[i][3] = rs.getString(4);
				i++;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		dt.setDataVector(data, columnNames);
		
		
	}

}
